<?php

namespace app\order\logic;

use think\facade\Db;
use think\facade\Cache;
use app\common\library\Excel;
use think\exception\ValidateException;
use think\facade\Log;
use util\Token;
use util\Upload;
use think\cache\driver\Redis;
use app\purchase\logic\PlanSuggestLogic;

class OrderReportSkuLogic
{

    public function __construct()
    {

    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 列表
     * @author zhangheng
     */
    public function index($params = [])
    {
        //实时查询
        $limit          = $params['limit'] ?? 10;
        $date           = $params['date'] ?? "";
        $spu            = $params['spu'] ?? "";
        $develop_type   = $params['develop_type'] ?? "";
        $customDate     = $params['customDate'] ?? 0;
        $adminId        = $params['adminId'] ?? "";
        $groupId        = $params['groupId'] ?? "";
        $shop_id        = $params['shop_id'] ?? "";

        $sku_report     = Config('Logisticskey.sku_report');

        if($groupId==11 && !in_array($adminId,$sku_report)){
            $data = [
                'list'  => [],
                'total' => 0,
            ];
        }else{
            $where = [];
            //customDate优先
            if(!empty($customDate)){
                switch ($customDate) {
                    case '1':
                        //实时
                        $yesterday = date('Y-m-d', time() - timezone());
                        $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                        $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                        $where[]  = ['a.createtime', '>=', $midnightYesterday];
                        $where[]  = ['a.createtime', '<=', $endOfYesterday];

                        $date = $yesterday ."到".$yesterday;
                        $date_msg = $yesterday;

                        break;
                    case '2':
                        $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                        $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                        $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                        $where[]  = ['a.createtime', '>=', $midnightYesterday];
                        $where[]  = ['a.createtime', '<=', $endOfYesterday];

                        $date = $yesterday ."到".$yesterday;
                        $date_msg = $yesterday;

                        break;
                    case '3':
                        $yesterday = strtotime(date('Y-m-d', time() - timezone()));
                        $yesterdayEnd = strtotime(date('Y-m-d', strtotime('-6 day') - timezone()));
                        $where[] = ['a.createtime', '<=', $yesterday];
                        $where[] = ['a.createtime', '>=', $yesterdayEnd];

                        $date = date('Y-m-d',$yesterdayEnd) ."到".date('Y-m-d 23:59:59',$yesterday) ;
                        $date_msg = date('Y-m-d',$yesterdayEnd) ."到".date('m-d',$yesterday);

                        break;
                    default:
                        $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                        $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                        $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                        $where[]  = ['a.createtime', '>=', $midnightYesterday];
                        $where[]  = ['a.createtime', '<=', $endOfYesterday];

                        $date = $yesterday ."到".$yesterday;
                        $date_msg = $yesterday;
                }
            }else{
                // 当自定义时间为空时 查日期有没有值
                if (!empty($date)) {
                    if($date['0'] == $date['1']){
                        $date_msg = $date['0'];
                    }else{
                        $date_msg = $date['0'] ."到".date('m-d', strtotime($date['1']));
                    }
                    $where[] = ['a.createtime', '>=', strtotime($date['0'])];
                    $where[] = ['a.createtime', '<=', strtotime($date['1']) + 3600*24-1];
                    $date = $date['0'] ."到".date('Y-m-d 00:00:00', strtotime($date['1']));
                }else{
                    $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                    $where[]  = ['a.createtime', '>=', $midnightYesterday];
                    $where[]  = ['a.createtime', '<=', $endOfYesterday];
                    $date = $yesterday ."到".$yesterday;
                    $date_msg = $yesterday;
                }
            }

            if (!empty($spu)) {
                $spu = trim($spu);
                $where[] = ['a.spu', 'like', '%' . $spu . '%'];
            }

            if($shop_id){
                $where[] = ['a.shop_id', '=',$shop_id];
            }


            if($develop_type){
                $spus = Db::name('product')->where('develop_type',$develop_type)->column('spu');
                $where[] = ['a.spu', 'in',$spus];
            }

            //排序
            $AfterSales = $params['AfterSales'] ?? "";
            $AfterSalesRate = $params['AfterSalesRate'] ?? "";
            $RefundTotal = $params['RefundTotal'] ?? "";
            $RefundTotalRate = $params['RefundTotalRate'] ?? "";
            $skuquantityTotal = $params['skuquantityTotal'] ?? "";
            $skuquantity = $params['skuquantity'] ?? "";
            if($AfterSales){
                $order['f.AfterSales'] = $AfterSales;
            }
            if($AfterSalesRate){
                $order['f.AfterSalesRate'] = $AfterSalesRate;
            }
            if($RefundTotal){
                $order['f.RefundTotal'] = $RefundTotal;
            }
            if($RefundTotalRate){
                $order['f.RefundTotalRate'] = $RefundTotalRate;
            }
            if($skuquantityTotal){
                $order['f.skuquantityTotal'] = $skuquantityTotal;
            }
            if($skuquantity){
                $order['skuquantity'] = $skuquantity;
            }else{
                $order['skuquantity'] = 'desc';
            }
            $order['a.spu'] = 'desc';

            $where[]  = ['a.financial_status', 'in', ["partially_paid", "paid", "partially_refunded", "refunded"]];
            $where[] = ['a.is_seel', '<>',1];
            $list = Db::name('order_skuinfo')
                ->field('
                sum(a.realcount) as skuquantity,
                a.spu,f.AfterSales,f.AfterSalesRate,f.RefundTotal,f.RefundTotalRate,f.skuquantityTotal
            ')
                ->alias('a')
                ->join('qu_order_feedback f','a.spu = f.spu', 'left')
                ->where($where)
                ->group('a.spu')
                ->order($order)
                ->paginate($limit);
            
            $lists = $list->items();
            //批量获取sku图片;
            $spus  = array_column($lists,'spu');
            //批量获取图片地址;
            $products     = Db::name('product')->field('spu,product_images,develop_type,product_type_1,product_type_2,product_type_3')->whereIn('spu',$spus)->select()->toArray();
            $products_map = array_column($products,null,'spu');
            $product_level     = Db::name('product_level')->field('type_id,type_name')->select()->toArray();
            $product_level_map = array_column($product_level,'type_name','type_id');
            foreach ($lists as $k=>$v) {
                $lists[$k]['date_msg'] = $date_msg;
                $lists[$k]['date'] = $date;
                $spu                 = strtolower($v['spu']);
                $product_images      = $products_map[$spu]??[];
                $product_develop     = $product_images['develop_type']??'1';
                $product_images_info = $product_images['product_images']??[];
                if (isset($product_images['product_type_1'])) {
                    $lists[$k]['product_type_1'] = $product_level_map[$product_images['product_type_1']] ?? "";
                }else{
                    $lists[$k]['product_type_1'] = '';
                }
                if (isset($product_images['product_type_2'])) {
                    $lists[$k]['product_type_2'] = $product_level_map[$product_images['product_type_2']] ?? "";
                }else{
                    $lists[$k]['product_type_2'] = '';
                }
                if (isset($product_images['product_type_3'])) {
                    $lists[$k]['product_type_3'] = $product_level_map[$product_images['product_type_3']] ?? "";
                }else{
                    $lists[$k]['product_type_3'] = '';
                }
                $current_images = $product_images_info ? current(json_decode($product_images_info)) : '';
                $lists[$k]['product_images'] = $current_images;
                $lists[$k]['develop_type']   = $product_develop;
                $sku_price = self::get_spu_price_info($v['spu']);
                $lists[$k]['price'] = '$'.getRes($sku_price*$v['skuquantity']);

                $lists[$k]['AfterSales'] = $v['AfterSales'] ?? 0;
                $v['AfterSalesRate'] = $v['AfterSalesRate'] ?? 0;
                $lists[$k]['AfterSalesRate'] = $v['AfterSalesRate'].'%';
                $lists[$k]['RefundTotal'] = $v['RefundTotal'] ?? 0;
                $v['RefundTotalRate'] = $v['RefundTotalRate'] ?? 0;
                $lists[$k]['RefundTotalRate'] = $v['RefundTotalRate'].'%';
                $lists[$k]['skuquantityTotal'] = $v['skuquantityTotal'] ?? 0;

            }

            $data = [
                'list' => $lists,
                'total' => $list->total(),
            ];
        }


        return json(DefaultResponse(200, true, '获取成功', $data));
    }

    /**
    * @desc    查询售后件数
    * @author  zhangheng
    */
    public function AfterSalesFeedbackList($spus)
    {
        $result = Db::name('order_skuinfo')
            ->alias('a')
            ->join('qu_order_achievement c', 'a.order_id = c.order_id', 'left')
            ->join('qu_order_qustioninfo d', 'a.spu = d.spu AND a.order_id = d.order_id', 'left') // 改正此处连接条件
            ->field('a.spu,
            sum(case when c.financial_status in ("partially_refunded", "refunded", "partially_paid") 
            AND c.fulfillment_status in ("fulfilled", "restocked")
            then a.realcount else 0 end) as RefundTotal,
            sum(a.realcount) as skuquantity,
            sum(case when d.spu IS NOT NULL 
            AND d.qustion_id_parent != 5 
            then a.realcount else 0 end) as OrderFeedbackTotal')
            ->where('a.spu','in',$spus)
            ->group('a.spu')
            ->select()
            ->toArray();
        return array_column($result,null,'spu');
    }
    
    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 导出
     * @author zhangheng
     */
    public function skuinfo($params = [])
    {
        $date = $params['date'] ?? "";
        $spu = $params['spu'] ?? "";
        
        $where[]  = ['spu', '=', $spu]; 
        $where[]  = ['financial_status', 'in', ["partially_paid", "paid", "partially_refunded", "refunded"]];

        if (!empty($date)) {
            if (strpos($date, '到') !== false){
                $date = explode('到',$date);
                $where[] = ['createtime', '>=', strtotime($date['0'])];
                $where[] = ['createtime', '<=', strtotime($date['1']) + 3600*24-1];
            }else{
                $where[] = ['createtime', '>=', strtotime($date)];
                $where[] = ['createtime', '<=', strtotime($date) + 3600*24-1];
            }
        }else{
            $yesterday = date('Y-m-d', time() - timezone());
            $midnightYesterday = strtotime($yesterday . ' 00:00:00');
            $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
            $where[]  = ['createtime', '>=', $midnightYesterday];
            $where[]  = ['createtime', '<=', $endOfYesterday]; 
        }


        $lists = Db::name('order_skuinfo')
            ->field('
                sum(realcount) as count,
                new_sku as sku
            ')
            ->where($where)
            ->group('new_sku')
            ->order('count desc')
            ->select()->toarray();
            
        $colorCounts = [];
        $sizeCounts = [];
        $Counts_Total = 0;
        foreach ($lists as $item) {
            if(empty($item['sku'])){
                continue;
            } 
            // 颜色
            list($spu,$color, $size) = explode('-', $item['sku'], 3);  
            $spu = trim($spu);
            $color = trim($color);
            $size = trim($size);
            if (!isset($colorCounts[$color])) {  
                $colorCounts[$color] = (int)$item['count'];  
            }else{
                $colorCounts[$color] = $colorCounts[$color] + (int)$item['count'];
            }
            //尺码  
            if (!isset($sizeCounts[$size])) {  
                $sizeCounts[$size] = (int)$item['count'];  
            }else{
                $sizeCounts[$size] = $sizeCounts[$size] + (int)$item['count']; 
            }
            $Counts_Total += (int)$item['count']; 
        }

        $colors = [];
        if(!empty($colorCounts)){
            foreach ($colorCounts as $k=>$v) {
                $Proportion = number_format((float)$v / (float)$Counts_Total*100, 2, '.', '')."%";
                $colors[] = [
                    'name' => $k.":".$v."(".$Proportion.")",  
                    'count' => $v  
                ];  
            }
        }

        $sizeCounts_new = [];
        foreach ($sizeCounts as $k=>$v) {
            $sizeCounts_new[] = [
                'size' => $k,
                'count' => $v
            ];
        }
        // halt($sizeCounts_new);
        try{ 
            //针对36E,38E,38D,36D,38F,40E,40F
            usort($sizeCounts_new, function($a, $b) {
                preg_match('/^(\d+)(\w+)$/', $a['size'], $matchesA);
                preg_match('/^(\d+)(\w+)$/', $b['size'], $matchesB);
                $numA = (int)$matchesA[1];
                $charA = $matchesA[2];
                $numB = (int)$matchesB[1];
                $charB = $matchesB[2];
                if ($numA != $numB) {
                    return $numA - $numB;
                }
                return strcmp($charA, $charB);
            });
        } catch (\Exception $e) {
            
        }
        // halt($sizeCounts_new);
        try{
            //针对'XS','S','XS/S','S/M','M','M/L', 'L', 'L/XL', 'XL'
            $sizeCounts_new = (new PlanSuggestLogic())->sort_size($sizeCounts_new);
        } catch (\Exception $e) {
            
        }
        $sizeCounts = array_column($sizeCounts_new, "count", 'size');
        // halt($sizeCounts_new);
        
        $cups = [];
        if(!empty($sizeCounts)){
            foreach ($sizeCounts as $k=>$v) {
                $Proportion = number_format((float)$v / (float)$Counts_Total*100, 2, '.', '')."%";
                $cups[] = [
                    'name' => $k.":".$v."(".$Proportion.")",  
                    'count' => $v  
                ];
            }
        }
        // halt($cups);

        $data = [
            'spu' => $lists,
            'color' => $colors,
            'cup' => $cups,
        ];
        return $data;
    }

    /**
    * @desc    sku销量排名
    * @date    2024/7/16
    * @author  zhangheng
    */
    public function spuSalesRanking($params = [])
    {
        $date          = $params['date'] ?? "";
        $spu           = $params['spu'] ?? "";
        $customDate    = $params['customDate'] ?? 0;
        if (empty($spu)) {
            return json(DefaultResponse(201, false, 'SPU不能为空'));
        }
        
        $where[]  = ['spu', '=', $spu]; 
        $where[]  = ['financial_status', 'in', ["partially_paid", "paid", "partially_refunded", "refunded"]];

        if($customDate){
            switch ($customDate) {
                case '1':
                    $yesterday    = strtotime(date('Y-m-d', time()));
                    $yesterdayEnd = strtotime(date('Y-m-d', strtotime('-7 day')));
                    $where[] = ['createtime', '<=', $yesterday];
                    $where[] = ['createtime', '>=', $yesterdayEnd];
                    break;
                case '2':
                    $yesterday    = strtotime(date('Y-m-d', time()));
                    $yesterdayEnd = strtotime(date('Y-m-d', strtotime('-15 day')));
                    $where[] = ['createtime', '<=', $yesterday];
                    $where[] = ['createtime', '>=', $yesterdayEnd];
                    break;
                case '3':
                    $yesterday    = strtotime(date('Y-m-d', time()));
                    $yesterdayEnd = strtotime(date('Y-m-d', strtotime('-30 day')));
                    $where[] = ['createtime', '<=', $yesterday];
                    $where[] = ['createtime', '>=', $yesterdayEnd];
                    break;
                default:
                    $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                    $where[]  = ['createtime', '>=', $midnightYesterday];
                    $where[]  = ['createtime', '<=', $endOfYesterday];
            }
        }else{
            if (!empty($date)){
                $where[] = ['createtime', '>=', strtotime($date['0'])];
                $where[] = ['createtime', '<=', strtotime($date['1']) + 3600*24-1];
            }else{
                $yesterday = strtotime(date('Y-m-d', time() + 3600*24 - timezone()));
                $yesterdayEnd = strtotime(date('Y-m-d', strtotime('-8 day') - timezone()));
                $where[] = ['createtime', '<=', $yesterday];
                $where[] = ['createtime', '>=', $yesterdayEnd];
            }
        }
// halt($where);
        $list = Db::name('order_skuinfo')
            ->field('
            DATE_FORMAT(FROM_UNIXTIME(createtime), "%Y-%m-%d") as date,
            sum(a.realcount) as quantity')
            ->alias('a')
            // ->join('qu_order c','a.order_id = c.order_id', 'left')
            ->group('date')  
            ->where($where)
            ->order('date asc')
            ->order(['date' => 'asc','quantity' => 'desc'])
            ->select()  
            ->toArray();
        foreach ($list as &$v) {
            $v['quantity'] = (int)$v['quantity'];
        }
            

        return json(DefaultResponse(200, true, '获取成功', $list));
    }
    
    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 导出
     * @author zhangheng
     */
    public function export_cost($params = [])
    {
        $header = [];
        $header[] = ['SPU', 'spu'];
        $header[] = ['SKU件数', 'skuquantity'];
        $header[] = ['成交额($)', 'price'];
        $header[] = ['售后件数', 'AfterSales'];
        $header[] = ['售后率', 'AfterSalesRate'];
        $header[] = ['退款件数', 'RefundTotal'];
        $header[] = ['退款率', 'RefundTotalRate'];
        $header[] = ['SPU当月成本', 'spucost'];
        $tableName = "spu_export";

        //实时查询
        $date           = $params['date'] ?? "";
        $spu            = $params['spu'] ?? "";
        $develop_type   = $params['develop_type'] ?? "";
        $customDate     = $params['customDate'] ?? 0;

        $where = [];

        //customDate优先
        if(!empty($customDate)){
            switch ($customDate) {
                case '1':
                    //实时
                    $yesterday = date('Y-m-d', time() - timezone());  
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                    $where[]  = ['a.createtime', '>=', $midnightYesterday];
                    $where[]  = ['a.createtime', '<=', $endOfYesterday];

                    $date = $yesterday ."到".$yesterday;
                    $date_msg = $yesterday;

                    break;
                case '2':
                    $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                    $where[]  = ['a.createtime', '>=', $midnightYesterday];
                    $where[]  = ['a.createtime', '<=', $endOfYesterday];

                    $date = $yesterday ."到".$yesterday;
                    $date_msg = $yesterday;

                    break;
                case '3':
                    $yesterday = strtotime(date('Y-m-d', time() - timezone()));  
                    $yesterdayEnd = strtotime(date('Y-m-d', strtotime('-6 day') - timezone()));  
                    $where[] = ['a.createtime', '<=', $yesterday];
                    $where[] = ['a.createtime', '>=', $yesterdayEnd];

                    $date = date('Y-m-d',$yesterdayEnd) ."到".date('Y-m-d 23:59:59',$yesterday) ;
                    $date_msg = date('Y-m-d',$yesterdayEnd) ."到".date('m-d',$yesterday);

                    break;
                default:
                    $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                    $where[]  = ['a.createtime', '>=', $midnightYesterday];
                    $where[]  = ['a.createtime', '<=', $endOfYesterday]; 

                    $date = $yesterday ."到".$yesterday;
                    $date_msg = $yesterday;
            }
        }else{
            // 当自定义时间为空时 查日期有没有值
            if (!empty($date)) {
                if($date['0'] == $date['1']){
                    $date_msg = $date['0'];
                }else{
                    $date_msg = $date['0'] ."到".date('m-d', strtotime($date['1']));
                }
                $where[] = ['a.createtime', '>=', strtotime($date['0'])];
                $where[] = ['a.createtime', '<=', strtotime($date['1']) + 3600*24-1];
                $date = $date['0'] ."到".date('Y-m-d 23:59:59', strtotime($date['1']));
            }else{
                $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                $where[]  = ['a.createtime', '>=', $midnightYesterday];
                $where[]  = ['a.createtime', '<=', $endOfYesterday]; 
                $date = $yesterday ."到".$yesterday;
                $date_msg = $yesterday;
            }
        }

        if (!empty($spu)) {
            $spu = trim($spu);
            $where[] = ['a.spu', 'like', '%' . $spu . '%'];
        }

        if($develop_type){
            $spus = Db::name('product')->where('develop_type',$develop_type)->column('spu');
            $where[] = ['a.spu', 'in',$spus];
        }
        
        $where[]  = ['a.financial_status', 'in', ["partially_paid", "paid", "partially_refunded", "refunded"]];

        $lists = Db::name('order_skuinfo')
            ->field('
                sum(a.realcount) as skuquantity,
                a.spu,f.AfterSales,f.AfterSalesRate,f.RefundTotal,f.RefundTotalRate,f.skuquantityTotal
            ')
            ->alias('a')
            ->join('qu_order_feedback f','a.spu = f.spu', 'left')
            ->where($where)
            ->group('a.spu')
            ->order('skuquantity desc')
            ->select()->toarray();

        $spucost_map = [];
        if(!empty($date)){
            $firstDashPos = strpos($date, '-');
            $secondDashPos = strpos($date, '-', $firstDashPos + 1);
            $new_data = substr($date, 0, $secondDashPos);
            $spucost = Db::name('ordercheck_spucost')->field('spu,material')->where('date',$new_data)->select()->toarray();
            $spucost_map = array_column($spucost,'material','spu');
        }
        
        //批量获取sku图片;
        $spus  = array_column($lists,'spu');
        foreach ($lists as $k=>$v) {
            $sku_price = self::get_spu_price_info($v['spu']);
            $lists[$k]['price'] = getRes($sku_price*$v['skuquantity']);

            $lists[$k]['AfterSales'] = $v['AfterSales'] ?? 0;
            $v['AfterSalesRate'] = $v['AfterSalesRate'] ?? 0;
            $lists[$k]['AfterSalesRate'] = $v['AfterSalesRate'].'%';
            $lists[$k]['RefundTotal'] = $v['RefundTotal'] ?? 0;
            $v['RefundTotalRate'] = $v['RefundTotalRate'] ?? 0;
            $lists[$k]['RefundTotalRate'] = $v['RefundTotalRate'].'%';
            $lists[$k]['skuquantityTotal'] = $v['skuquantityTotal'] ?? 0;
            $lists[$k]['spucost'] = $spucost_map[$v['spu']] ?? 0;
        }
        $fileName = $tableName.random_int(100, 999);
        return Excel::exportData($lists, $header, $fileName, 'xlsx');
    }

    
    
    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 导出
     * @author zhangheng
     */
    public function export($params = [])
    {
        $header = [];
        $header[] = ['SPU', 'spu'];
        $header[] = ['SKU件数', 'skuquantity'];
        $header[] = ['产品一级分类', 'product_type_1'];
        $header[] = ['产品二级分类', 'product_type_2'];
        $header[] = ['产品三级分类', 'product_type_3'];
        $header[] = ['成交额($)', 'price'];
        $header[] = ['历史总件数', 'skuquantityTotal'];
        $header[] = ['总售后件数', 'AfterSales'];
        $header[] = ['总售后率', 'AfterSalesRate'];
        $header[] = ['总退款件数', 'RefundTotal'];
        $header[] = ['总退款率', 'RefundTotalRate'];
        // $header[] = ['SPU当月成本', 'spucost'];
        $tableName = "spu_export";

        //实时查询
        $date           = $params['date'] ?? "";
        $spu            = $params['spu'] ?? "";
        $develop_type   = $params['develop_type'] ?? "";
        $customDate     = $params['customDate'] ?? 0;
        $adminId        = $params['adminId'] ?? "";
        $groupId        = $params['groupId'] ?? "";
        $shop_id        = $params['shop_id'] ?? "";

        $sku_report     = Config('Logisticskey.sku_report');

        if($groupId==11 && !in_array($adminId,$sku_report)){
            $lists = [];
        }else{
            $where = [];
            //customDate优先
            if(!empty($customDate)){
                switch ($customDate) {
                    case '1':
                        //实时
                        $yesterday = date('Y-m-d', time() - timezone());
                        $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                        $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                        $where[]  = ['a.createtime', '>=', $midnightYesterday];
                        $where[]  = ['a.createtime', '<=', $endOfYesterday];

                        $date = $yesterday ."到".$yesterday;
                        $date_msg = $yesterday;

                        break;
                    case '2':
                        $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                        $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                        $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                        $where[]  = ['a.createtime', '>=', $midnightYesterday];
                        $where[]  = ['a.createtime', '<=', $endOfYesterday];

                        $date = $yesterday ."到".$yesterday;
                        $date_msg = $yesterday;

                        break;
                    case '3':
                        $yesterday = strtotime(date('Y-m-d', time() - timezone()));
                        $yesterdayEnd = strtotime(date('Y-m-d', strtotime('-6 day') - timezone()));
                        $where[] = ['a.createtime', '<=', $yesterday];
                        $where[] = ['a.createtime', '>=', $yesterdayEnd];

                        $date = date('Y-m-d',$yesterdayEnd) ."到".date('Y-m-d 23:59:59',$yesterday) ;
                        $date_msg = date('Y-m-d',$yesterdayEnd) ."到".date('m-d',$yesterday);

                        break;
                    default:
                        $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                        $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                        $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                        $where[]  = ['a.createtime', '>=', $midnightYesterday];
                        $where[]  = ['a.createtime', '<=', $endOfYesterday];

                        $date = $yesterday ."到".$yesterday;
                        $date_msg = $yesterday;
                }
            }else{
                // 当自定义时间为空时 查日期有没有值
                if (!empty($date)) {
                    if($date['0'] == $date['1']){
                        $date_msg = $date['0'];
                    }else{
                        $date_msg = $date['0'] ."到".date('m-d', strtotime($date['1']));
                    }
                    $where[] = ['a.createtime', '>=', strtotime($date['0'])];
                    $where[] = ['a.createtime', '<=', strtotime($date['1']) + 3600*24-1];
                    $date = $date['0'] ."到".date('Y-m-d 23:59:59', strtotime($date['1']));
                }else{
                    $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                    $where[]  = ['a.createtime', '>=', $midnightYesterday];
                    $where[]  = ['a.createtime', '<=', $endOfYesterday];
                    $date = $yesterday ."到".$yesterday;
                    $date_msg = $yesterday;
                }
            }

            if (!empty($spu)) {
                $spu = trim($spu);
                $where[] = ['a.spu', 'like', '%' . $spu . '%'];
            }

            if($shop_id){
                $where[] = ['a.shop_id', '=',$shop_id];
            }


            if($develop_type){
                $spus = Db::name('product')->where('develop_type',$develop_type)->column('spu');
                $where[] = ['a.spu', 'in',$spus];
            }

            //排序
            $AfterSales = $params['AfterSales'] ?? "";
            $AfterSalesRate = $params['AfterSalesRate'] ?? "";
            $RefundTotal = $params['RefundTotal'] ?? "";
            $RefundTotalRate = $params['RefundTotalRate'] ?? "";
            $skuquantityTotal = $params['skuquantityTotal'] ?? "";
            $skuquantity = $params['skuquantity'] ?? "";
            if($AfterSales){
                $order['f.AfterSales'] = $AfterSales;
            }
            if($AfterSalesRate){
                $order['f.AfterSalesRate'] = $AfterSalesRate;
            }
            if($RefundTotal){
                $order['f.RefundTotal'] = $RefundTotal;
            }
            if($RefundTotalRate){
                $order['f.RefundTotalRate'] = $RefundTotalRate;
            }
            if($skuquantityTotal){
                $order['f.skuquantityTotal'] = $skuquantityTotal;
            }
            if($skuquantity){
                $order['skuquantity'] = $skuquantity;
            }else{
                $order['skuquantity'] = 'desc';
            }
            $order['a.spu'] = 'desc';

            $where[]  = ['a.financial_status', 'in', ["partially_paid", "paid", "partially_refunded", "refunded"]];

            $lists = Db::name('order_skuinfo')
                ->field('
                sum(a.realcount) as skuquantity,
                a.spu,f.AfterSales,f.AfterSalesRate,f.RefundTotal,f.RefundTotalRate,f.skuquantityTotal
            ')
                ->alias('a')
                ->join('qu_order_feedback f','a.spu = f.spu', 'left')
                ->where($where)
                ->group('a.spu')
                ->order('skuquantity asc')
                ->select()->toarray();
            //批量获取sku图片;
            $spus  = array_column($lists,'spu');
            //批量获取图片地址;
            $products     = Db::name('product')->field('spu,product_images,develop_type,product_type_1,product_type_2,product_type_3')->whereIn('spu',$spus)->select()->toArray();
            $products_map = array_column($products,null,'spu');
            $product_level     = Db::name('product_level')->field('type_id,type_name')->select()->toArray();
            $product_level_map = array_column($product_level,'type_name','type_id');
            foreach ($lists as $k=>$v) {
                $lists[$k]['date_msg'] = $date_msg;
                $lists[$k]['date'] = $date;
                $spu                 = strtolower($v['spu']);
                $product_images      = $products_map[$spu]??[];
                $product_develop     = $product_images['develop_type']??'1';
                $product_images_info = $product_images['product_images']??[];
                if (isset($product_images['product_type_1'])) {
                    $lists[$k]['product_type_1'] = $product_level_map[$product_images['product_type_1']] ?? "";
                }else{
                    $lists[$k]['product_type_1'] = '';
                }
                if (isset($product_images['product_type_2'])) {
                    $lists[$k]['product_type_2'] = $product_level_map[$product_images['product_type_2']] ?? "";
                }else{
                    $lists[$k]['product_type_2'] = '';
                }
                if (isset($product_images['product_type_3'])) {
                    $lists[$k]['product_type_3'] = $product_level_map[$product_images['product_type_3']] ?? "";
                }else{
                    $lists[$k]['product_type_3'] = '';
                }
                $current_images = $product_images_info ? current(json_decode($product_images_info)) : '';
                $lists[$k]['product_images'] = $current_images;
                $lists[$k]['develop_type']   = $product_develop;
                $sku_price = self::get_spu_price_info($v['spu']);
                $lists[$k]['price'] = getRes($sku_price*$v['skuquantity']);

                $lists[$k]['AfterSales'] = $v['AfterSales'] ?? 0;
                $v['AfterSalesRate'] = $v['AfterSalesRate'] ?? 0;
                $lists[$k]['AfterSalesRate'] = $v['AfterSalesRate'].'%';
                $lists[$k]['RefundTotal'] = $v['RefundTotal'] ?? 0;
                $v['RefundTotalRate'] = $v['RefundTotalRate'] ?? 0;
                $lists[$k]['RefundTotalRate'] = $v['RefundTotalRate'].'%';
                $lists[$k]['skuquantityTotal'] = $v['skuquantityTotal'] ?? 0;

            }
        }
        $fileName = $tableName.random_int(100, 999);
        return Excel::exportData($lists, $header, $fileName, 'xlsx');
    }
    
    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 导出
     * @author zhangheng
     */
    public function export_skuinfo($params = [])
    {
        $header = [];
        $header[] = ['SKU', 'sku'];
        $header[] = ['件数', 'count'];
        
        $tableName = "OrderReportDaily";

        $date = $params['date'] ?? "";
        $spu = $params['spu'] ?? "sha226";
        
        $where[]  = ['spu', '=', $spu]; 
        $where[]  = ['financial_status', 'in', ["partially_paid", "paid", "partially_refunded", "refunded"]];

        if (!empty($date)) {
            if (strpos($date, '到') !== false){
                $date = explode('到',$date);
                $where[] = ['createtime', '>=', strtotime($date['0'])];
                $where[] = ['createtime', '<=', strtotime($date['1']) + 3600*24-1];
            }else{
                $where[] = ['createtime', '>=', strtotime($date)];
                $where[] = ['createtime', '<=', strtotime($date) + 3600*24-1];
            }
        }else{
            $yesterday = date('Y-m-d', time() - timezone());
            $midnightYesterday = strtotime($yesterday . ' 00:00:00');
            $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
            $where[]  = ['createtime', '>=', $midnightYesterday];
            $where[]  = ['createtime', '<=', $endOfYesterday]; 
        }


        $lists = Db::name('order_skuinfo')
            ->field('
                sum(realcount) as count,
                new_sku as sku
            ')
            ->where($where)
            ->group('new_sku')
            ->order('count desc')
            ->select()->toarray();

        $header = [];
        $header[] = ['SKU', 'sku'];
        $header[] = ['件数', 'count'];

        $colorCounts = [];
        $sizeCounts = [];
        $Counts_Total = 0;
        foreach ($lists as $item) {
            if(empty($item['sku'])){
                continue;
            } 
            // 颜色
            list($spu,$color, $size) = explode('-', $item['sku'], 3);  
            if (!isset($colorCounts[$color])) {  
                $colorCounts[$color] = (int)$item['count'];  
            }else{
                $colorCounts[$color] = $colorCounts[$color] + (int)$item['count'];
            }
            //尺码  
            if (!isset($sizeCounts[$size])) {  
                $sizeCounts[$size] = (int)$item['count'];  
            }else{
                $sizeCounts[$size] = $sizeCounts[$size] + (int)$item['count']; 
            }
            $Counts_Total += (int)$item['count']; 
        }

        $header_arr['sku'] = "";
        $header_arr['count'] = "";
        $lists[] = $header_arr;
        $header_arr['sku'] = "颜色";
        $header_arr['count'] = "占比";
        $lists[] = $header_arr;

        if(!empty($colorCounts)){
            foreach ($colorCounts as $k=>$v) {
                $Proportion = number_format((float)$v / (float)$Counts_Total*100, 2, '.', '')."%";
                $lists[] = [
                    'sku' => $k,  
                    'count' => $Proportion,
                ];  
            }
        }

        $header_arr['sku'] = "";
        $header_arr['count'] = "";
        $lists[] = $header_arr;
        $header_arr['sku'] = "尺码";
        $header_arr['count'] = "占比";
        $lists[] = $header_arr;
        
        if(!empty($sizeCounts)){
            foreach ($sizeCounts as $k=>$v) {
                $Proportion = number_format((float)$v / (float)$Counts_Total*100, 2, '.', '')."%";
                $lists[] = [
                    'sku' => $k,  
                    'count' => $Proportion,
                ];
            }
        }

        $fileName = "export_" . $tableName . "_" . time();
        return Excel::exportData($lists, $header, $fileName, 'xlsx');
    }

    public function export_skuinfo_test($params = [])
    {
        $header = [];
        $header[] = ['SKU', 'sku'];
        $header[] = ['件数', 'count'];

        $tableName = "OrderReportDaily";

        $lists = Db::name('checkbill_777')
            ->field('sku,nums as count')
            ->group('sku')
            ->select()->toarray();

        $header = [];
        $header[] = ['SKU', 'sku'];
        $header[] = ['件数', 'count'];

        $colorCounts = [];
        $sizeCounts = [];
        $Counts_Total = 0;
        foreach ($lists as $item) {
            if(empty($item['sku'])){
                continue;
            }
            // 颜色
            list($spu,$color, $size) = explode('-', $item['sku'], 3);
            if (!isset($colorCounts[$color])) {
                $colorCounts[$color] = (int)$item['count'];
            }else{
                $colorCounts[$color] = $colorCounts[$color] + (int)$item['count'];
            }
            //尺码
            if (!isset($sizeCounts[$size])) {
                $sizeCounts[$size] = (int)$item['count'];
            }else{
                $sizeCounts[$size] = $sizeCounts[$size] + (int)$item['count'];
            }
            $Counts_Total += (int)$item['count'];
        }

        $header_arr['sku'] = "";
        $header_arr['count'] = "";
        $lists[] = $header_arr;
        $header_arr['sku'] = "颜色";
        $header_arr['count'] = "占比";
        $lists[] = $header_arr;

        if(!empty($colorCounts)){
            foreach ($colorCounts as $k=>$v) {
                $Proportion = number_format((float)$v / (float)$Counts_Total*100, 2, '.', '')."%";
                $lists[] = [
                    'sku' => $k,
                    'count' => $Proportion,
                ];
            }
        }

        $header_arr['sku'] = "";
        $header_arr['count'] = "";
        $lists[] = $header_arr;
        $header_arr['sku'] = "尺码";
        $header_arr['count'] = "占比";
        $lists[] = $header_arr;

        if(!empty($sizeCounts)){
            foreach ($sizeCounts as $k=>$v) {
                $Proportion = number_format((float)$v / (float)$Counts_Total*100, 2, '.', '')."%";
                $lists[] = [
                    'sku' => $k,
                    'count' => $Proportion,
                ];
            }
        }

        $fileName = "export_" . $tableName . "_" . time();
        return Excel::exportData($lists, $header, $fileName, 'xlsx');
    }



    /**
     * @desc    获取spu单价
     * @date    2024/8/22
     * @author  luckyXiong
     */
    public function get_spu_price($spu=''){

        $res = Cache::store('redis')->get('get_spu_price'.$spu);
        if (!empty($res)) { 
            return $res;
        }

        $res =  Db::name('order_spu_price')->where(['spu'=>$spu])->value('price');
        if(!$res){
            $res = '28.26';
        }
        Cache::store('redis')->set('get_spu_price'.$spu, $res, 3600 * 24 * 3); // 缓存24小时  
        return  $res;
    }


    /**
     * @desc    获取spu单价
     * @date    2024/8/22
     * @author  luckyXiong
     */
    public function get_spu_price_info($spu=''){
        $key   = "spu_price_single";
        $list  = Cache::store('redis')->get($key);
        if(!$list){
            $list = Db::name('order_spu_price')->field('spu,price')->select()->toArray();
            Cache::store('redis')->set($key,$list,86400*7);
        }

        $list_map   = array_column($list,null,'spu');

        $price_info = $list_map[$spu]??[];

        $price      = $price_info['price']??'28.26';
        return $price;
    }


    
    
    /**
     * @desc 4-8月单款单件sku
     * @author zhangheng
     */
    public function export_sku($params = [])
    {
        $header = [];
        $header[] = ['日期', 'date'];
        $header[] = ['SKU', 'sku'];
        $header[] = ['SKU件数', 'quantity'];
        $tableName = "spu_export";

        $where = [];
        $where[]  = ['createtime', '>=', 1743436800];
        $where[]  = ['createtime', '<=', 1753977600];
        $where[]  = ['sku', 'like', "%bra%"];
        $where[]  = ['financial_status', 'in', ["partially_paid", "paid", "partially_refunded", "refunded"]];
        
        $lists = Db::name('order_skuinfo')
            ->field('
            DATE_FORMAT(FROM_UNIXTIME(createtime), "%Y-%m-%d") as date,
            sum(a.realcount) as quantity,sku')
            ->alias('a')
            ->group('date,sku')  
            ->where($where)
            // ->limit(1000)
            ->order(['date' => 'asc','quantity' => 'desc'])
            ->select()  
            ->toArray();
        // halt($lists);
        $fileName = $tableName.random_int(100, 999);
        return Excel::exportData($lists, $header, $fileName, 'xlsx');
    }


    /**
     * @desc 文胸1-8月的尺码颜色明细
     * @author zhangheng
     */
    // public function export($params = [])
    public function export_color($params = [])
    {
        $header = [];
        $header[] = ['size', 'size'];
        $header[] = ['cup', 'cup'];
        $header[] = ['SKU件数', 'quantity'];
        $header[] = ['比例', 'Proportion'];
        $tableName = "spu_export";

        $where = [];
        $where[]  = ['b.createtime', '>=', 1735660800];
        $where[]  = ['b.createtime', '<=', time()];
        $where[]  = ['a.spu', 'like', "%bra%"];
        $where[]  = ['b.financial_status', 'in', ["partially_paid", "paid", "partially_refunded", "refunded"]];
        $where[]  = ['a.size', '<>', ""];

        $lists = Db::name('order_skuinfo')
            ->field('
            sum(a.realcount) as quantity,a.size,a.cup')
            ->alias('a')
            ->join('qu_order_achievement b','b.order_id = a.order_id', 'left')
            ->group('a.size,a.cup')  
            ->where($where)
            ->order(['quantity' => 'desc']) 
            ->select()  
            ->toArray();

        $lists_total = Db::name('order_skuinfo')
            ->field('
            sum(a.realcount) as quantity')
            ->alias('a')
            ->join('qu_order_achievement b','b.order_id = a.order_id', 'left')
            ->where($where)
            ->order(['quantity' => 'desc'])
            ->select()  
            ->toArray();
        $quantity = $lists_total[0]['quantity']??0;
            foreach ($lists as &$v) {
                if(!empty($quantity)){
                    $v['Proportion'] = number_format((float)$v['quantity'] / (float)$quantity*100, 2, '.', '')."%";
                }else{
                    $v['Proportion'] = "0.00%";
                }
            }
        $fileName = $tableName.random_int(100, 999);
        return Excel::exportData($lists, $header, $fileName, 'xlsx');
    }

    

}
